package database;

import item.Item;
import item.PurchaseItem;
import item.SalesReportItem;
import item.ShipItem;
import item.ShoppingCartItem;
import item.TopSellingItem;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;
import java.sql.Date;
import java.util.List;

import amsStore.Bill;
import amsStore.OnlineReceipt;
import amsStore.Receipt;

import user.Customer;

public class TableManager {

	private final String CREATE_TABLES_SCRIPT = System.getProperty("user.dir") + "\\scripts\\create_tables.sql";
	private final String DROP_TABLES_SCRIPT = System.getProperty("user.dir") + "\\scripts\\drop_tables.sql";
	private final String SUPPLIER_DATA_SCRIPT = System.getProperty("user.dir") + "\\scripts\\insert_supplier_data.sql";
	private final String STORE_DATA_SCRIPT = System.getProperty("user.dir") + "\\scripts\\insert_store_data.sql";
	private final String SHIPMENT_DATA_SCRIPT = System.getProperty("user.dir") + "\\scripts\\insert_shipment_data.sql";
	private final String SHIP_ITEM_DATA_SCRIPT = System.getProperty("user.dir") + "\\scripts\\insert_ship_item_data.sql";
	private final String LEAD_SINGER_DATA_SCRIPT = System.getProperty("user.dir") + "\\scripts\\insert_lead_singer_data.sql";
	private final String ITEM_DATA_SCRIPT = System.getProperty("user.dir") + "\\scripts\\insert_item_data.sql";
	private final String HAS_SONG_DATA_SCRIPT = System.getProperty("user.dir") + "\\scripts\\insert_has_song_data.sql";
	
	private final String SALES_REPORT_QUERY = "select I.upc, category, sellPrice, SUM(quantity) from Item I, PurchaseItem Q, Purchase P " +
			"WHERE I.upc = Q.upc AND P.receiptId = Q.receiptId AND \"date\" = %s AND sname = '%s' GROUP by I.category, I.upc, I.sellPrice ORDER BY I.category";
	
	private final String ITEM_SEARCH_QUERY = "SELECT I.upc FROM Item I, LeadSinger L " +
			"WHERE I.upc = L.upc AND I.title = '%s' AND I.category = '%s' AND L.name = '%s'";
	
	private final String TOP_SELLING_ITEMS_QUERY = "SELECT upc, totalQuantity, stockLeft FROM (SELECT Q.upc, SUM(quantity) as totalQuantity, SUM(stock) as stockLeft " +
			"FROM Purchase P, PurchaseItem Q, Stored S WHERE \"date\" = %s AND P.receiptID = Q.receiptId AND Q.upc = S.upc GROUP BY Q.upc ORDER BY SUM(quantity) DESC) WHERE rownum <= %s";
	
	private final String DELIMITER = ";";
	
	private static final String DATE_FORMAT = "to_date('%s', 'yyyy-mm-dd')";
	
	// instance of TableManager for implementation of singleton pattern
	private static TableManager tableManager = null;

	// connection to the DB
	private DBConnection dbConn = null;
	
	// constants for order processing times
	private final int ORDERS_PROCESSED_PER_DAY = 2;
	private final long MILLISECONDS_PER_DAY = 24 * 3600 * 1000;
	
	List<Table> tableList = null;
	
	public enum TableEnum
	{
		Item,
		LeadSinger,
		HasSong,
		Supplier,
		Shipment,
		ShipItem,
		Store,
		Stored,
		Purchase,
		PurchaseItem,
		Customer,
		Return,
		ReturnItem,
	};
	
	// currently logged in Customer (if any)
	private Customer curCustomer = null;
	
	private TableManager()
	{
		dbConn = DBConnection.getInstance();
		
		tableList = new ArrayList<Table>(TableEnum.values().length);
		tableList.add(TableEnum.Item.ordinal(), new ItemTable());
		tableList.add(TableEnum.LeadSinger.ordinal(), new LeadSingerTable());		
		tableList.add(TableEnum.HasSong.ordinal(), new HasSongTable());
		tableList.add(TableEnum.Supplier.ordinal(), new SupplierTable());
		tableList.add(TableEnum.Shipment.ordinal(), new ShipmentTable());
		tableList.add(TableEnum.ShipItem.ordinal(), new ShipItemTable());		
		tableList.add(TableEnum.Store.ordinal(), new StoreTable());
		tableList.add(TableEnum.Stored.ordinal(), new StoredTable());
		tableList.add(TableEnum.Purchase.ordinal(), new PurchaseTable());
		tableList.add(TableEnum.PurchaseItem.ordinal(), new PurchaseItemTable());
		tableList.add(TableEnum.Customer.ordinal(), new CustomerTable());
		tableList.add(TableEnum.Return.ordinal(), new ReturnTable());
		tableList.add(TableEnum.ReturnItem.ordinal(), new ReturnItemTable());
	}
	
	public static TableManager getInstance()
	{
		if (tableManager == null)
		{
			tableManager = new TableManager();
		}
		
		return tableManager;
	}
	
	/**
	 * CreateTables reads in a .sql script containing the
	 * statements for creating all required tables
	 * 
	 * @return if the tables were successfully created
	 */
	public void createTables() throws SQLException
	{
		List<String> sqlStmts = readSqlFile(CREATE_TABLES_SCRIPT);		
		for (String stmt : sqlStmts)
		{			
			dbConn.executeStatement(stmt);
		}	
	}
	
	public void dropTables() throws SQLException
	{
		List<String> sqlStmts = readSqlFile(DROP_TABLES_SCRIPT);		
		for (String stmt : sqlStmts)
		{
			dbConn.executeStatement(stmt);			
		}	
	}
	
	public void populateTables() throws SQLException
	{
		List<String> dataStmt = null;
		
		try
		{
			dataStmt = readSqlFile(ITEM_DATA_SCRIPT);
			for (String stmt : dataStmt)
			{
				dbConn.executeStatement(stmt);			
			}
			
			dataStmt = readSqlFile(SUPPLIER_DATA_SCRIPT);
			for (String stmt : dataStmt)
			{
				dbConn.executeStatement(stmt);			
			}
			
			dataStmt = readSqlFile(STORE_DATA_SCRIPT);
			for (String stmt : dataStmt)
			{
				dbConn.executeStatement(stmt);			
			}
			
			dataStmt = readSqlFile(SHIPMENT_DATA_SCRIPT);
			for (String stmt : dataStmt)
			{
				dbConn.executeStatement(stmt);			
			}
			
			dataStmt = readSqlFile(SHIP_ITEM_DATA_SCRIPT);
			for (String stmt : dataStmt)
			{
				dbConn.executeStatement(stmt);			
			}
			
			dataStmt = readSqlFile(LEAD_SINGER_DATA_SCRIPT);
			for (String stmt : dataStmt)
			{
				dbConn.executeStatement(stmt);			
			}	
			
			dataStmt = readSqlFile(HAS_SONG_DATA_SCRIPT);
			for (String stmt : dataStmt)
			{
				dbConn.executeStatement(stmt);			
			}			
		
			dbConn.commit();
		}
		catch(SQLException e)
		{
			dbConn.rollback();
			throw e;
		}
	}
		
	public void insert(TableEnum table, List<String> insertValues) throws SQLException
	{
		Table curTable = tableList.get(table.ordinal());
		try
		{
			curTable.insert(insertValues);
			dbConn.commit();
		}
		catch(SQLException e)
		{
			dbConn.rollback();
			throw e;
		}		
	}	
	
	public void delete(TableEnum table, List<String> deleteKeyValues) throws SQLException
	{
		Table curTable = tableList.get(table.ordinal());
		try
		{
			curTable.delete(deleteKeyValues);
			dbConn.commit();
		}
		catch(SQLException e)
		{
			dbConn.rollback();
			throw e;			
		}			
	}	
	
	public List<List<String>> display(TableEnum table) throws SQLException
	{
		List<List<String>> tableRowList = null;
		
		Table curTable = tableList.get(table.ordinal());
		tableRowList = curTable.display();
		
		return tableRowList;
	}
		
	private List<String> readSqlFile(String sqlFileName)
	{
		List<String> stmts = new ArrayList<String>();
		StringBuilder stmtSB = new StringBuilder();
		String line = null;
		
		try {
		
			// read in the create table script
			BufferedReader br = new BufferedReader(new FileReader(sqlFileName));
			
			// read each line and append to the string builder
			while(( line = br.readLine()) != null)
			{
				// check if the line contains delimiter
				if(line.contains(DELIMITER))
				{
					line = line.replace(DELIMITER, "");
					stmtSB.append(line + "\n");
					
					stmts.add(stmtSB.toString());
					stmtSB.delete(0, stmtSB.length());
				}
				else
				{
					stmtSB.append(line + "\n");
				}				
			}
			
			br.close();
			
			return stmts;
			
		}
		catch (IOException e) {
			System.out.println("Message: " + e.getMessage());
			return null;
		}		
	}

	public boolean customerLogin(String cid, String password) throws Exception
	{
		CustomerTable ct = (CustomerTable) tableList.get(TableEnum.Customer.ordinal());
		curCustomer = ct.login(cid, password);
		
		return curCustomer != null;
	}

	public void customerRegistration(String cid, String password, String name, String address, String phone) throws SQLException
	{
		CustomerTable ct = (CustomerTable) tableList.get(TableEnum.Customer.ordinal());
		
		try
		{
			ct.register(cid, password, name, address, phone);
			dbConn.commit();
		}
		catch(SQLException e)
		{
			dbConn.rollback();
			throw e;
		}
	}
	
	public Set<Item> customerItemSearch(String category, String title, String leadSinger) throws SQLException
	{
		Set<Item> searchResults = new HashSet<Item>();
		
		LeadSingerTable lst = (LeadSingerTable) tableList.get(TableEnum.LeadSinger.ordinal());
		ItemTable it = (ItemTable) tableList.get(TableEnum.Item.ordinal());
		
		if(!category.equals("") && !title.equals("") && !leadSinger.equals(""))
		{
			String fmtdQuery = String.format(ITEM_SEARCH_QUERY, title, category, leadSinger);
			ResultSet rs = dbConn.executeQuery(fmtdQuery);
			
			if(rs != null)
			{
				if(rs.next())
				{
					String upc = rs.getString(1);
					searchResults.add(it.getItemFromUpc(upc));				
				}
			}
		}
		else
		{			
			// find upcs for each items with leadSinger
			if(!leadSinger.equals(""))
			{
				List<String> upcsForSinger = lst.findUpcsForSinger(leadSinger);
				for(String curUpc : upcsForSinger)
				{
					// for each upc found, add the item to search results
					searchResults.add(it.getItemFromUpc(curUpc));			
				}
			}
			
			if(!category.equals(""))
			{
				// find all items with the given category
				searchResults.addAll(it.getItemsFromCategory(category));
			}
			
			if(!title.equals(""))
			{
				// find all items with the given title
				searchResults.addAll(it.getItemsFromTitle(title));
			}
		}	
				
		return searchResults;
	}
	
	public OnlineReceipt customerCheckOut(String cid, Date date, Bill checkoutBill, String cardNum, Date expiryDate) throws SQLException
	{
		try
		{
			StoredTable st = (StoredTable) tableList.get(TableEnum.Stored.ordinal());
			PurchaseTable pt = (PurchaseTable) tableList.get(TableEnum.Purchase.ordinal());
			PurchaseItemTable pit = (PurchaseItemTable) tableList.get(TableEnum.PurchaseItem.ordinal());
			
			// find out how many outstanding orders there are in the PurchaseTable
			int outstandingOrders = pt.getNumOutstandingOnlineOrders();
			int numDaysForDelivery = outstandingOrders / ORDERS_PROCESSED_PER_DAY;
			
			// calculate expected delivery date
			String purchaseDateString = date.toString();
			long curDateTime = date.getTime();
			long newDateTime = curDateTime + numDaysForDelivery * MILLISECONDS_PER_DAY;
									
			Date expectedDeliveryDate = new Date(newDateTime);
			
			String expectedDeliveryDateString = expectedDeliveryDate.toString();			
			String expiryDateString = expiryDate.toString();
			
			// insert a new entry into the purchase table
			pt.onlinePurchase(cid, purchaseDateString, cardNum, expiryDateString, expectedDeliveryDateString);
			
			int receiptId = pt.getCurrentReceiptId();
			
			// insert entries into the purchaseItem table for each item purchased
			List<ShoppingCartItem> checkoutItems = checkoutBill.getBillItems();
			List<PurchaseItem> purchasedItems = new ArrayList<PurchaseItem>(checkoutItems.size());
			for(ShoppingCartItem curCheckoutItem : checkoutItems)
			{
				// create a PurchaseItem from the current ShoppingCartItem
				PurchaseItem curPurchaseItem = new PurchaseItem(receiptId, curCheckoutItem);
				purchasedItems.add(curPurchaseItem);
				
				int curQuantity = curPurchaseItem.getQuantity();
				
				// add entry for the current purchase item into the purchaseItem table
				String upcString = curPurchaseItem.getUpc();
				String quantityString = String.valueOf(curQuantity);
				
				pit.purchaseItem(upcString, quantityString);
				
				int curStock = st.getStockStored("Warehouse", upcString);
				int newStock = curStock - curQuantity; 				
				
				// update quantity in Stored table
				st.updateStock("Warehouse", upcString, newStock);
			}
			
			// create a new OnlineReceipt from the list of items
			String lastFiveDigits = cardNum.substring(cardNum.length() - 5);		
			
			OnlineReceipt receipt = new OnlineReceipt(numDaysForDelivery, receiptId, date, purchasedItems, lastFiveDigits);
						
			dbConn.commit();
			
			return receipt;
		}
		catch(SQLException e)
		{
			dbConn.rollback();
			
			throw e;
		}
	}
	
	public int customerGetOnlineQuantityAvailable(String upc) throws SQLException
	{
		StoredTable st = (StoredTable) tableList.get(TableEnum.Stored.ordinal());		
		return st.getStockStored("Warehouse", upc);
	}
	
	public Receipt clerkProcessPurchase(List<ShoppingCartItem> purchaseList, String storeName, Date curDate, boolean creditPurchase, 
			String cardNum, Date expiryDate) throws Exception 
	{
		try
		{
			StoredTable st = (StoredTable) tableList.get(TableEnum.Stored.ordinal());
			PurchaseTable pt = (PurchaseTable) tableList.get(TableEnum.Purchase.ordinal());
			PurchaseItemTable pit = (PurchaseItemTable) tableList.get(TableEnum.PurchaseItem.ordinal());
			
			// insert a new entry into the purchase table
			String expiryDateString = null;		
			if(creditPurchase)
			{				
				if(expiryDate != null)
				{
					expiryDateString = expiryDate.toString();
				}
				else
				{
					throw new Exception("Invalid credit card expiration date");
				}
				
				if(cardNum == null || cardNum.equals(""))
				{
					throw new Exception("Invalid credit card number");
				}
			}	
			
			pt.inStorePurchase(curDate.toString(), storeName, cardNum, expiryDateString);				
			int receiptId = pt.getCurrentReceiptId();
			
			List<PurchaseItem> purchaseItemList = new ArrayList<PurchaseItem>();
			for(ShoppingCartItem curPurchaseItem : purchaseList)
			{
				int quantityRequested = curPurchaseItem.getQuantity();
				String upc = curPurchaseItem.getUpc();
				
				// check that the store has enough stock of the current item for the purchase
				int curStock = st.getStockStored(storeName, upc);
				if(curStock >= quantityRequested) {
					//UPDATE Quantity in Store
					int newStock = curStock - quantityRequested;
					st.updateStock(storeName, upc, newStock);
				}			
				else {
					throw new Exception("Not enough stock of item: " + curPurchaseItem.getTitle());
				}
				
				// add a new entry to the purchase item table
				pit.purchaseItem(String.valueOf(curPurchaseItem.getUpc()), String.valueOf(curPurchaseItem.getQuantity()));
				
				//add item to list
				purchaseItemList.add(new PurchaseItem(receiptId, curPurchaseItem));
			}
			
			// commit DB changes
			dbConn.commit();
				
			//Get Receipt info
			//Convert cardNum to get last 5 digits
			String lastFiveCard = null;
			if(creditPurchase)
			{
				lastFiveCard =  cardNum.substring(cardNum.length() - 5);			
			}
			
			return new Receipt(receiptId, curDate, purchaseItemList, creditPurchase, lastFiveCard);		
		}
		catch(Exception e)
		{
			// if any error happens, we need to rollback ALL changes
			dbConn.rollback();
			
			// re-throw the exception for UI to handle
			throw e;
		}
		
	}
	
	public float clerkProcessReturnFromReceiptId(String receiptId, Date curDate, String storeName, String upc, int stockReturned) throws Exception {
		try
		{
			PurchaseTable pt = (PurchaseTable) tableList.get(TableEnum.Purchase.ordinal());
			PurchaseItemTable pit = (PurchaseItemTable) tableList.get(TableEnum.PurchaseItem.ordinal());
			ReturnTable rt = (ReturnTable) tableList.get(TableEnum.Return.ordinal());
			ReturnItemTable rit = (ReturnItemTable) tableList.get(TableEnum.ReturnItem.ordinal());
			ItemTable it = (ItemTable) tableList.get(TableEnum.Item.ordinal());

			float totalCredited = 0;
			
			//Check date of receipt
			if(!pt.checkReturnDate(receiptId, curDate))
			{
				throw new Exception("This purchase exceeds the 15 day return period.");
			}
			
			//add items to return
			rt.createReturn(curDate.toString(), receiptId, storeName);
			
			//insert into returnedItem
			rit.addReturnItem(upc, stockReturned);
			
			//remove from purchaseItem
			pit.returnPurchasedItem(receiptId, upc);
			
			//Get Sell Price
			Item getSellPrice = it.getItemFromUpc(upc);
			totalCredited = getSellPrice.getSellPrice();		
						
			// commit DB changes
			dbConn.commit();
			
			return totalCredited;
		}
		catch(SQLException e)
		{
			// if any error happens, we need to rollback ALL changes
			dbConn.rollback();
			
			// re-throw the exception for UI to handle
			throw e;
		}
	}
		
	
	public List<String> clerkGetStoreNames() throws SQLException
	{
		StoreTable st = (StoreTable) tableList.get(TableEnum.Store.ordinal());
		return st.getStoreNames();
	}
	
	public ShoppingCartItem clerkGetShoppingCartItem(String upc, int quantity) throws SQLException
	{
		ItemTable it = (ItemTable) tableList.get(TableEnum.Item.ordinal());
		Item item = it.getItemFromUpc(upc);		
		
		return new ShoppingCartItem(quantity, item);
	}
	
	public void managerAddSupplier(String name, String address, String city, String status) throws SQLException
	{
		SupplierTable st = (SupplierTable) tableList.get(TableEnum.Supplier.ordinal());
		try
		{
			st.addSupplier(name, address, city, status);
			dbConn.commit();
		}
		catch(SQLException e)
		{
			dbConn.rollback();
			throw e;
		}		
	}
	
	public void managerRemoveSupplier(String name) throws SQLException
	{
		SupplierTable st = (SupplierTable) tableList.get(TableEnum.Supplier.ordinal());
		try
		{
			st.removeSupplier(name); 
			dbConn.commit();
		}
		catch(SQLException e)
		{
			dbConn.rollback();
			throw e;
		}
	}
	
	public List<ShipItem> managerGetItemsInShipment(int sid) throws SQLException
	{
		ShipItemTable sit = (ShipItemTable) tableList.get(TableEnum.ShipItem.ordinal());
		return sit.getShipItemsFromSid(sid);		
	}
	
	public void managerProcessShipment(int sid) throws SQLException
	{
		ShipmentTable st = (ShipmentTable) tableList.get(TableEnum.Shipment.ordinal());
		ShipItemTable sit = (ShipItemTable) tableList.get(TableEnum.ShipItem.ordinal());
		ItemTable it = (ItemTable) tableList.get(TableEnum.Item.ordinal());
		StoredTable sdt = (StoredTable) tableList.get(TableEnum.Stored.ordinal());
		
		try
		{
			// determine the storename of the shipment
			String storeName = st.getStoreNameFromSid(sid);
			
			// 1. select all shipItems in the ShipItem table with sid
			List<ShipItem> shipItems = sit.getShipItemsFromSid(sid);
			for(ShipItem curShipItem : shipItems)
			{
				String curUpc = curShipItem.getUpc();
				
				float supPrice = curShipItem.getSupPrice();
				float newSellPrice = supPrice * 1.20f;
				
				// update the price
				it.updateSellPrice(curUpc, newSellPrice);
								
				// c. update the entry in the Stored table with the new quantity, a new entry may have to be added
				//	  if the store didn't previously carry the item
				if(sdt.checkIfStoredEntryExists(storeName, curUpc))
				{
					int curQuantity = sdt.getStockStored(storeName, curUpc);
					int addedQuantity = curShipItem.getQuantity();
					int newQuantity = curQuantity + addedQuantity;
					
					sdt.updateStock(storeName, curUpc, newQuantity);
				}
				else
				{
					// create a new entry
					int quantity = curShipItem.getQuantity();					
					sdt.insertStoredEntry(storeName, curUpc, quantity);
				}
				
				// d. remove the entry from the ShipItem table
				sit.removeShipItemEntry(sid, curUpc);				
			}
			
			// 3. Remove the entry from the Shipment table
			st.removeShipmentEntry(sid);
			
			dbConn.commit();
			
		}
		catch(SQLException e)
		{
			dbConn.rollback();
			throw e;
		}
	}
	
	public void managerProcessOrderDeliveries(Date expectedDate, Date currentDate) throws Exception
	{
		try
		{
			PurchaseTable pt = (PurchaseTable) tableList.get(TableEnum.Purchase.ordinal());
			
			// make sure current date >= expectedDate
						
			// calculate expected delivery date
			long expectedDateTime = expectedDate.getTime();
			long currentDateTime = currentDate.getTime();
			
			if(currentDateTime < expectedDateTime)
			{
				throw new Exception("Can't deliver orders until on or after " + expectedDate.toString());
			}
			
			if(!pt.checkForUndelieveredOrders(expectedDate))
			{
				throw new Exception("No orders to deliver for: " + expectedDate.toString()); 
			}
			
			pt.updateDeliveredDates(expectedDate, currentDate);
			
			// commit DB changes
			dbConn.commit();
		}
		catch(Exception e)
		{
			// if any error happens, we need to rollback ALL changes
			dbConn.rollback();
			
			// re-throw the exception for UI to handle
			throw e;
		}
	}
	
	public List<SalesReportItem> managerGetDailySalesReport(String storeName, Date date) throws SQLException
	{
		List<SalesReportItem> salesReport = new ArrayList<SalesReportItem>();
		
		String dateString = String.format(DATE_FORMAT, date.toString());		
		String fmtdQuery = String.format(SALES_REPORT_QUERY, dateString, storeName);		
		ResultSet rs = dbConn.executeQuery(fmtdQuery);
		
		if(rs != null)
		{
			while(rs.next())
			{
				String upc = rs.getString(1);
				String category = rs.getString(2);
				float unitPrice = rs.getFloat(3);
				int units = rs.getInt(4);
				
				salesReport.add(new SalesReportItem(upc, category, unitPrice, units));
			}
			
			rs.close();
		}
		
		return salesReport;
	}
	
	public List<TopSellingItem> managerGetTopSellingItems(Date date, int numItems) throws SQLException
	{
		List<TopSellingItem> topSellers = new ArrayList<TopSellingItem>(numItems);
		
		ItemTable it = (ItemTable) tableList.get(TableEnum.Item.ordinal());
		
		String dateString = String.format(DATE_FORMAT, date.toString());		
		String fmtdQuery = String.format(TOP_SELLING_ITEMS_QUERY, dateString, String.valueOf(numItems));
		
		ResultSet rs = dbConn.executeQuery(fmtdQuery);
		
		if(rs != null)
		{
			while(rs.next())
			{
				String upc = rs.getString(1);
				int copiesSold = rs.getInt(2);
				int stockLeft = rs.getInt(3);
				
				Item curItem = it.getItemFromUpc(upc);
				topSellers.add(new TopSellingItem(copiesSold, stockLeft, curItem));
			}
		}
		
		return topSellers;
	}

	public List<String> getTablePrimaryKeyColumnNames(TableEnum tableEnum) 
	{
		Table table = tableList.get(tableEnum.ordinal());		
		return table.getPrimaryKeyColumnNames();
	}
	
	public List<String> getTableColumnNames(TableEnum tableEnum)
	{
		Table table = tableList.get(tableEnum.ordinal());
		return table.getColumnNames();
	}
	
}
